# encoding:utf-8
import pymysql
import sshtunnel
from pymysql.constants import CLIENT


class Mysql:

    conn = None
    cursor = None

    def __init__(self, host, username, password, db, ssh_host, ssh_username, ssh_password, charset='utf8', port=3306, sshport=22):
        self.host = host
        self.username = username
        self.password = password
        self.db = db
        self.ssh_host = ssh_host
        self.ssh_username = ssh_username
        self.ssh_password = ssh_password
        self.charset = charset
        self.port = port
        self.sshport = sshport

    # 连接MySQL
    def connect(self):
        server = sshtunnel.SSHTunnelForwarder(
            (self.ssh_host, 22),  # 作为跳转机的ip和端口
            ssh_username=self.ssh_username,  # 跳转机的用户名
            ssh_password=self.ssh_password,  # 对应的密码
            remote_bind_address=(self.host, 3306),  # 数据库的IP地址和端口
            # local_bind_address=('127.0.0.1', 3306)#端口可以自己设置，没有冲突即可，不加这个参数也可
        )
        server.start()

        self.conn = pymysql.connect(host='127.0.0.1',  # 必须为本机回环地址，原因会在下面写出
                               port=server.local_bind_port,
                               user=self.username,
                               password = self.password,  # 数据库密码
                               database = self.db,
                               charset='utf8',
                               client_flag=CLIENT.MULTI_STATEMENTS,
                               )
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)  # 取出的值变成字典
        # self.cursor = self.conn.cursor()

    def connectOnly(self):
        server = sshtunnel.SSHTunnelForwarder(
            (self.ssh_host, 22),  # 作为跳转机的ip和端口
            ssh_username=self.ssh_username,  # 跳转机的用户名
            ssh_password=self.ssh_password,  # 对应的密码
            remote_bind_address=(self.host, 3306),  # 数据库的IP地址和端口
            # local_bind_address=('127.0.0.1', 3306)#端口可以自己设置，没有冲突即可，不加这个参数也可
        )
        server.start()
        self.conn = pymysql.connect(host='127.0.0.1',  # 必须为本机回环地址，原因会在下面写出
                                    port=server.local_bind_port,
                                    user=self.username,
                                    password=self.password,  # 数据库密码
                                    database=self.db,
                                    charset='utf8',
                                    client_flag=CLIENT.MULTI_STATEMENTS,
                                    )
        return self.conn


    # 关闭MySQL
    def close(self):
        self.cursor.close()
        self.conn.close()

    # 查（单个）
    def get(self, sql, params=()):
        result = None
        print(111)
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result

    # 查（多个）
    def select(self, sql, params=()):
        list_data = ()
        try:
            self.connect()
            self.cursor.execute(sql, params)
            list_data = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return list_data

    # 增
    def insert(self, sql, params=()):
        return self.__edit(sql, params)

    # 改
    def update(self, sql, params=()):
        return self.__edit(sql, params)

    # 删
    def delete(self, sql, params=()):
        return self.__edit(sql, params)

    def __edit(self, sql, params):
        count = 0
        try:
            self.connect()
            count = self.cursor.execute(sql, params)
            self.conn.commit()
            self.close()
        except Exception as e:
            print(e)
        return count


if __name__ == '__main__':
    # sql = "SELECT logs, massage, create_date FROM admin_oa_hospital_log WHERE create_date BETWEEN %s AND %s AND massage LIKE %s"
    # params = ('2023-05-22 0:00:00', '2023-05-22 23:59:59', '%失败%')
    # # sql = "select content from sms_message where mobile='18017870857' order by id desc limit 1"
    # a = Mysql('10.23.92.253','developer-readonly','developer-readonly',
    #              'app-vaccine','106.75.225.123','root','talent911docs').select(sql,params)
    sql = f'select is_available from t_employee where mobile="13815363374"'
    a = Mysql('rm-uf6zhyey108d7k1p58o.mysql.rds.aliyuncs.com', 'developer-readonly', 'developer-readonly',
              'jz-admin', '106.75.225.123', 'root', 'talent911docs').select(sql)

